Option Strict On
Option Explicit On

Imports System
Imports KadGen.MetadataWrapper
Imports System.Text

Public Class MorphToBuild
    Private mDefaults As Defaults

    Public Function CreateBuildCollection( _
             ByVal datastructures As DbsDataStructureCollection, _
             ByVal mappingRoot As MapMappingRoot, _
             ByVal defaults As Defaults) _
             As BuildBuildCollection
        Dim buildCollection As New BuildBuildCollection

        For Each datastructure As DbsDataStructure In datastructures
            buildCollection.Add(CreateBuild(datastructure, mappingRoot, defaults))
        Next

        Return buildCollection
    End Function

    Public Function CreateBuild( _
             ByVal datastructure As DbsDataStructure, _
             ByVal mappingRoot As MapMappingRoot, _
             ByVal defaults As Defaults) _
             As BuildBuild
        Me.mDefaults = defaults

        Dim mapDataStructureName As String = datastructure.Name
        Dim name As String = mappingRoot.MapMapping.Name
        Dim build As New BuildBuild(mapDataStructureName, name)
        Utility.CopyCustomAttributes(mappingRoot, build, build)
        For Each objectDef As MapObjectDef In mappingRoot.MapMapping.MapObjectDefs
            AddSPSet(build, datastructure, objectDef)
        Next
        Return build
    End Function

    Protected Overridable Sub AddSPSet( _
               ByVal build As BuildBuild, _
               ByVal dataStructure As DbsDataStructure, _
               ByVal objectDef As MapObjectDef)
        Dim viewOrTable As IViewOrTable = dataStructure.GetViewOrTable(objectDef.TableName, objectDef.ViewName)
        If viewOrTable Is Nothing Then
            Utility.LogForUser("Can not find corresponding table", LogId.CanNotFindTable, objectDef)
        Else
            ' Create a new spset with from table
         Dim spset As New BuildSPSet(build.BuildBuild, False, viewOrTable.Name, viewOrTable.SingularName, _
                              viewOrTable.Name, viewOrTable.OriginalName, mDefaults.DefaultGenerate, _
                              False, viewOrTable.Schema, Nothing, Nothing, Nothing, objectDef.IsMultiParentChild, _
                              objectDef.TableNameColumnName, objectDef.PrimaryKeyColumnName)
            Utility.CopyCustomAttributes(objectDef, spset, build)
            build.BuildSPSets.Add(spset)
            'build.add(spset)
            If objectDef IsNot Nothing Then
                ' TODO: Add null support to load, make all items nullable, and check for nulls in the following
                spset.NoChildren = objectDef.NoChildren
                If objectDef.Name IsNot Nothing Then
                    spset.Name = objectDef.Name
                End If
                spset.ObjectName = objectDef.ObjectName
                spset.Generate = objectDef.Generate
                spset.NoGenStoredProc = objectDef.NoGenStoredProc
                spset.IsChild = objectDef.IsChild
                spset.IsParent = objectDef.IsParent
                spset.HasSelfJoin = objectDef.HasSelfJoin
            End If

            AddSearchStuff(spset, viewOrTable, objectDef)
            If Not String.IsNullOrEmpty(objectDef.TableName) Then
                AddForeignKeys(spset, dataStructure, CType(viewOrTable, DbsTable), objectDef)
            End If
            AddPrivileges(spset, viewOrTable, objectDef)
            AddSetSelect(spset, viewOrTable, objectDef)
            AddRecordset(spset, viewOrTable, objectDef)
            AddStoredProcs(spset, viewOrTable, objectDef)
        End If

    End Sub

    Protected Overridable Sub AddSearchStuff( _
                ByVal spset As BuildSPSet, _
                ByVal viewOrTable As IViewOrTable, _
                ByVal objectdef As MapObjectDef)
        ' Output retrieve parameters and where clause. Default for retrieve params is primary key
        '      Me.OutputBuildSearchStuffXML(childNode)
        ' Use first for the retrieve criteria, use rest for extra sp's
        If objectdef.MapObjectSearchCriterias IsNot Nothing AndAlso objectdef.MapObjectSearchCriterias.Count > 0 Then
            Dim criteria As MapSearchCriteria = Nothing
            For Each testCriteria As MapSearchCriteria In objectdef.MapObjectSearchCriterias
                If Utility.IsCriteriaPrimaryKey(testCriteria, objectdef) Then
                    criteria = testCriteria
                    Exit For
                End If
            Next
            If criteria Is Nothing Then
                Utility.LogForUser("Can not find primary key criteria", LogId.CanNotFindCriteria, objectdef)
            Else
                Dim ordinal As Int32 = 0
                For Each criterion As MapSearchCriterion In criteria.MapSearchCriterions
                    Dim column As IViewOrTableColumn = viewOrTable.ViewOrTableColumns(criterion.ColumnName)
                    Dim prop As MapProperty = objectdef.MapProperties.GetProperty(criterion.Name)
                    ' TODO: Review why we aren't getting everythign from the property, not the column
                    Dim param As BuildRetrieveParameter = New BuildRetrieveParameter(spset.BuildBuild, column.Name, _
                             criterion.ColumnName, criterion.Name, prop.UseForDesc, column.OriginalName, _
                             column.Caption, ordinal, False, column.SqlType, column.NETType, _
                             column.MaxLength, column.IsPrimaryKey, Nothing, Nothing, Nothing, _
                             column.IsAutoIncrement, False)
                    ' No logical custom attributes
                    ordinal += 1
                    spset.BuildRetrieveParameters.Add(param)
                Next
            End If
        End If
        spset.BuildWhereClauses.Add(New BuildWhereClause(spset.BuildBuild, objectdef.WhereClause))
    End Sub

    Protected Overridable Sub AddForeignKeys( _
                ByVal spset As BuildSPSet, _
                ByVal datastructure As DbsDataStructure, _
                ByVal table As DbsTable, _
                ByVal objectdef As MapObjectDef)
        ' Output foreign key table list from table.tableconstraints.tablerelations.parentTables
        '      Me.ForeignKeys.OutputBuildXML(childNode)
        For Each foreignKey As MapForeignKey In objectdef.MapForeignKeys
            Dim foreignKeyTable As New BuildForeignKeyTable(spset.BuildBuild, foreignKey.Name)
            spset.BuildForeignKeyTables.Add(foreignKeyTable)
            For i As Int32 = 0 To foreignKey.MapRelationKeys.Count - 1
                Dim key As MapRelationKey = foreignKey.MapRelationKeys(i)
                Dim fkey As BuildForeignKey = GetNewKey(spset.BuildBuild, key.ChildColumn, key.ChildTable, _
                           key.ChildObject, key.ChildProperty, key.ParentColumn, key.ParentTable, _
                           key.ParentObject, key.ParentProperty, i)
                Utility.CopyCustomAttributes(key, fkey, spset.BuildBuild)
                foreignKeyTable.BuildForeignKeys.Add(fkey)
            Next
        Next
    End Sub

    Private Function GetNewKey(ByVal buildBuild As BuildBuild, _
                   ByVal childColumn As String, _
                   ByVal childTable As String, _
                   ByVal childObject As String, _
                   ByVal childProperty As String, _
                   ByVal parentColumn As String, _
                   ByVal parentTable As String, _
                   ByVal parentObject As String, _
                   ByVal parentProperty As String, _
                   ByVal ordinal As Int32) _
                   As BuildForeignKey
        If String.IsNullOrEmpty(childProperty) Then
            childProperty = childColumn
        End If
        If String.IsNullOrEmpty(parentProperty) Then
            parentProperty = parentColumn
        End If
        If String.IsNullOrEmpty(childObject) Then
            childObject = childObject
        End If
        If String.IsNullOrEmpty(parentObject) Then
            parentObject = parentObject
        End If
        Return New BuildForeignKey(buildBuild, childColumn, childTable, _
                 childObject, childProperty, parentColumn, parentTable, _
                 parentObject, parentProperty, ordinal)
    End Function

    Protected Overridable Sub AddPrivileges(ByVal spset As BuildSPSet, ByVal viewOrTable As IViewOrTable, ByVal objectdef As MapObjectDef)
        ' TODO: Output privleges. Not clear whether wrapped in a collection node
        '      Me.ObjectPrivileges.OutputBuildXML(childNode)
        ' TODO: Priveleges are not in the Build SPSet. CUrrently
        ' Privileges
        '     Privilege
        '        Grantee attribute
        '        Rights attribute
        '        Source attribute
        If objectdef.MapObjectPrivileges.Count > 0 Then
            For Each privilege As MapPrivilege In objectdef.MapObjectPrivileges
                ' TODO: Create new privlege
                ' TODO: Utility.CopyCustomAttributes
                'spset.
            Next
        Else
            For Each privilege As MapPrivilege In mDefaults.DefaultPriveleges
                ' TODO: Create new privlege
                ' TODO: Utility.CopyCustomAttributes
                'spset.
            Next
        End If

    End Sub

    Protected Overridable Sub AddSetSelect( _
             ByVal spset As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectdef As MapObjectDef)
        ' TODO: THis method recreates legacy behavior, which may need updating
        ' Output UseForDesc. You'll also have to create default values, from somewhere - looks like name/desc and primary key
        '      Me.outputBuildSetSelectXML(childNode)
        spset.BuildSetSelect = New BuildSetSelect(spset.BuildBuild, viewOrTable.Name, viewOrTable.Schema)
        'For Each prop As MapProperty In objectdef.MapProperties
        '   If prop.IsPrimaryKey And prop.TableName = objectdef.TableName Then
        '      Dim setSelect As New BuildSetSelectColumn(spset.BuildBuild, prop.Name, _
        '               prop.IsPrimaryKey, prop.ColumnName, prop.TableName, prop.SQLType, prop.NETType, _
        '               prop.NETType, prop.IsAutoIncrement)
        '      Utility.CopyCustomAttributes(prop, setSelect, setSelect.BuildBuild)
        '      spset.BuildSetSelect.BuildSetSelectColumns.Add(setSelect)
        '   End If
        'Next
        Dim found As Boolean = False
        Dim joinTables As New List(Of DbsTable)
        For Each desc As MapUseForDesc In objectdef.MapUseForDescs
            ' Find the first match in any table. The main table wins
            Dim prop As MapProperty = objectdef.MapProperties.GetProperty(desc.Name)
            If prop Is Nothing Then
                prop = objectdef.MapProperties.GetPropertyByColumnName(desc.ColumnName)
            End If
            If prop Is Nothing Then
                Utility.LogForUser("Description property not found", LogId.CanNotFindProperty, objectdef, desc)
            Else
                If Not String.IsNullOrEmpty(prop.ColumnName) Or mDefaults.UseUnbackedInSetSelect Then
                    'Dim descColumn As IViewOrTableColumn
                    Dim isInMainTable As Boolean
                    isInMainTable = String.Compare(prop.TableName, objectdef.OriginalName, StringComparison.InvariantCultureIgnoreCase) = 0
                    If Not isInMainTable Then
                        Dim joinTable As DbsTable = viewOrTable.DbsDataStructures(0).GetTable(desc.TableName)
                        If Not joinTables.Contains(joinTable) Then
                            joinTables.Add(joinTable)
                        End If
                    End If
                    found = True
                    Dim setSelect As New BuildSetSelectColumn(spset.BuildBuild, prop.Name, _
                             prop.IsPrimaryKey, prop.OriginalName, prop.TableName, prop.SQLType, prop.NETType, _
                             prop.NETType, prop.IsAutoIncrement)
                    'Dim setSelect As New BuildSetSelectColumn(spset.BuildBuild, descColumn.Name, _
                    '         descColumn.IsPrimaryKey, descColumn.OriginalName, desc.TableName, descColumn.SqlType, descColumn.NETType, _
                    '         descColumn.NETType, descColumn.IsAutoIncrement)
                    Utility.CopyCustomAttributes(prop, setSelect, setSelect.BuildBuild)
                    spset.BuildSetSelect.BuildSetSelectColumns.Add(setSelect)
                End If
            End If
        Next
        ' This is ugly, but we've got to enter the joins in teh same order they
        ' exist in, which may not be column order
        Dim joinList As New SortedList(Of Int32, IMapJoin)
        For Each joinTable As DbsTable In joinTables
            ' Add the joins
            Dim join As IMapJoin = objectdef.GetJoin(joinTable.OriginalName)
            Dim i As Int32 = 0
            Dim joinFound As Boolean = False
            For Each testJoin As IMapJoin In objectdef.MapJoinIns
                i += 1
                If testJoin Is join Then
                    joinFound = True
                    Exit For
                End If
            Next
            If Not joinFound Then
                i = 100
                For Each testJoin As IMapJoin In objectdef.MapJoinIns
                    i += 1
                    If testJoin Is join Then
                        joinFound = True
                        Exit For
                    End If
                Next
            End If
            joinList.Add(i, join)
        Next
        For Each join As IMapJoin In joinList.Values
            spset.BuildSetSelect.BuildJoins.Add(AddBuildJoin(spset, join, viewOrTable))
        Next
        If Not found Then
            ' Add all non-primary key properties
            For Each prop As MapProperty In objectdef.MapProperties
                If Not String.IsNullOrEmpty(prop.ColumnName) Or mDefaults.UseUnbackedInSetSelect Then
                    If Not prop.IsPrimaryKey.GetValueOrDefault AndAlso prop.TableName.ToLower() = objectdef.TableName.ToLower() Then
                        ' primary keys already added
                        Dim setSelect As New BuildSetSelectColumn( _
                                 spset.BuildBuild, prop.Name, _
                                 prop.IsPrimaryKey, prop.ColumnName, prop.TableName, prop.SQLType, prop.NETType, _
                                 prop.NETType, prop.IsAutoIncrement)
                        Utility.CopyCustomAttributes(prop, setSelect, setSelect.BuildBuild)
                        spset.BuildSetSelect.BuildSetSelectColumns.Add(setSelect)
                    End If
                End If
            Next
        End If
    End Sub

    Protected Overridable Sub AddRecordset( _
             ByVal spset As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectdef As MapObjectDef)
        Dim recSet As BuildBuildRecordset = AddRecordset("RecSet", _
                 New BuildWhereClause(spset.BuildBuild, objectdef.WhereClause), _
                 spset, viewOrTable, objectdef, String.Empty, objectdef.TableName, _
                 Nothing, Nothing, 0, False, Nothing)
        If objectdef.MapOrderBies IsNot Nothing AndAlso objectdef.MapOrderBies.Count > 0 Then
            For Each orderby As MapOrderBy In objectdef.MapOrderBies
                Dim buildOrderBy As New BuildOrderBy(spset.BuildBuild, orderby.Name, orderby.Ordinal, _
                         orderby.SortOrder.ToString())
                Utility.CopyCustomAttributes(orderby, buildOrderBy, spset.BuildBuild)
                recSet.BuildOrderBies.Add(buildOrderBy)
            Next
        End If
        AddChildRecordsets(spset, viewOrTable, objectdef)
    End Sub

    Protected Overridable Sub AddChildRecordsets( _
             ByVal spset As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectdef As MapObjectDef)
        Dim childOrdinal As Int32 = 0
        If Not objectdef.IsLookup.GetValueOrDefault Then
            For Each child As MapChild In objectdef.MapChildren
                childOrdinal += 1
                Dim childObjectDef As MapObjectDef = objectdef.MapMappingRoot.GetObjectDef(child.ObjectName)
                Dim whereClause As String = GetChildWhereClause(objectdef, child)
                Dim childWhereClause As String = child.ChildWhereClause
                If String.IsNullOrEmpty(whereClause) Then
                    whereClause = childWhereClause
                ElseIf Not String.IsNullOrEmpty(childWhereClause) Then
                    whereClause += " AND " & childWhereClause
                End If
                Dim childTable As DbsTable = viewOrTable.DbsDataStructures.GetTable(child.TableName)
                If spset IsNot Nothing And childObjectDef IsNot Nothing Then
                    ' Changed childALias because it is being treated here as a table alias, which it is not
                    ' Changed back because in some cases it does need to be a child alias.
                    Dim newRecordset As BuildBuildRecordset = AddRecordset( _
                             "RecSet" & childOrdinal.ToString(), _
                             New BuildWhereClause(spset.BuildBuild, whereClause), _
                             spset, childTable, childObjectDef, child.RelationType, child.Alias, _
                             childObjectDef.Name, child.TempChildTableName, _
                             child.ChildFlag.GetValueOrDefault(), _
                             child.IsHierarchy.GetValueOrDefault, child.Name)
                    Utility.CopyCustomAttributes(child, newRecordset, newRecordset.BuildBuild)
                    If child.MapOrderBies IsNot Nothing AndAlso child.MapOrderBies.Count > 0 Then
                        For Each orderby As MapOrderBy In child.MapOrderBies
                            Dim buildOrderBy As New BuildOrderBy(spset.BuildBuild, _
                                     orderby.Name, orderby.Ordinal, orderby.SortOrder.ToString())
                            Utility.CopyCustomAttributes(orderby, buildOrderBy, spset.BuildBuild)
                            newRecordset.BuildOrderBies.Add(buildOrderBy)
                        Next
                    End If
                    For Each relationKey As MapRelationKey In child.MapRelationKeys
                        Dim childKey As New BuildChildKey(spset.BuildBuild, _
                                    relationKey.ChildColumn, relationKey.ChildTable, relationKey.ChildAlias, _
                                    relationKey.ChildObject, relationKey.ChildProperty, _
                                    relationKey.ParentColumn, relationKey.ParentTable, relationKey.ParentAlias, _
                                    relationKey.ParentObject, relationKey.ParentProperty)
                        newRecordset.BuildChildKeys.Add(childKey)
                    Next
                End If
            Next
        End If
    End Sub
    Private Function GetChildWhereClause( _
             ByVal objectDef As MapObjectDef, _
             ByVal child As MapChild) _
             As String
        Dim whereClause As New StringBuilder
        Dim relationKeys As MapRelationKeyCollection = child.MapRelationKeys
        For i As Int32 = 0 To relationKeys.Count - 1
            Dim key As MapRelationKey = relationKeys(i)
            If i > 0 Then
                whereClause.Append(" AND ")
            End If
            ' NOTE: This is SQL Server specific 
            If objectDef.MapMappingRoot.UseSchemaInWhereClause.GetValueOrDefault Then
                whereClause.Append("@" & key.ParentProperty & "=[" & child.Schema & "].[" & key.ChildTable & "].[" & key.ChildColumn & "]")
            Else
                whereClause.Append("@" & key.ParentProperty & "=[" & key.ChildTable & "].[" & key.ChildColumn & "]")
            End If
        Next
        Return whereClause.ToString()
    End Function

    Private Function AddRecordset( _
             ByVal name As String, _
             ByVal whereClause As BuildWhereClause, _
             ByVal spSet As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectDef As MapObjectDef, _
             ByVal relationType As String, _
             ByVal leftAlias As String, _
             ByVal childSPSetName As String, _
             ByVal tempTableName As String, _
             ByVal childFlag As ULong, _
             ByVal isHierarchy As Boolean, _
             ByVal childName As String) _
             As BuildBuildRecordset
        Dim recordset As New BuildBuildRecordset( _
                 spSet.BuildBuild, name, _
                 objectDef.InsertPriority, childSPSetName, _
                 tempTableName, childFlag, isHierarchy, childName)
        spSet.BuildBuildRecordsets.Add(recordset)
        recordset.BuildWhereClause = whereClause

        Dim joinIns As New List(Of IMapJoin)
        Dim joinBases As New List(Of IMapJoin)
        For Each join As MapJoinIn In objectDef.MapJoinIns
            joinIns.Add(join)
            recordset.BuildJoins.Add(AddBuildJoin(spSet, join, viewOrTable))
        Next
        For Each join As MapJoinBase In objectDef.MapJoinBases
            joinBases.Add(join)
            recordset.BuildJoins.Add(AddBuildJoin(spSet, join, viewOrTable))
        Next

        Dim tableOrdinal As Int32 = AddMainTable(spSet, viewOrTable, objectDef, _
                 relationType, leftAlias, recordset)

        tableOrdinal = AddJoinTables(spSet, viewOrTable, objectDef, tableOrdinal, joinBases, "Base", recordset)
        tableOrdinal = AddJoinTables(spSet, viewOrTable, objectDef, tableOrdinal, joinIns, "In", recordset)

        AddTableColumns(spSet, viewOrTable, objectDef, leftAlias, recordset)

        Dim childOrdinal As Int32 = 0
        For Each child As MapChild In objectDef.MapChildren
            childOrdinal += 1
            '         AddChildRecordsets(spset, table, objectdef.TableName, tableOrdinal)
        Next

        Return recordset
    End Function

    Private Function AddMainTable( _
             ByVal spSet As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectDef As MapObjectDef, _
             ByVal relationType As String, _
             ByVal rightAlias As String, _
             ByVal recordset As BuildBuildRecordset) _
             As Int32

        Dim tableOrdinal As Int32 = 0
        Dim columnOrdinal As Int32 = 0
        Dim maintable As New BuildBuildTable(spSet.BuildBuild, tableOrdinal, _
                 viewOrTable.Name, viewOrTable.OriginalName, viewOrTable.Name, _
                 relationType, rightAlias, viewOrTable.Schema)
        tableOrdinal += 1
        recordset.BuildBuildTables.Add(maintable)
        For Each prop As MapProperty In objectDef.MapProperties
            ' Add Join base to the following
            If prop.TableName = viewOrTable.OriginalName Then
                Dim usefordesc As Boolean = (objectDef.MapUseForDescs(prop.Name) IsNot Nothing)
                columnOrdinal += 1
                Dim buildColumn As New BuildBuildColumn(spSet.BuildBuild, prop.Name, _
                         prop.ColumnName, prop.ColumnName, usefordesc, prop.OriginalName, _
                         prop.Caption, columnOrdinal, prop.AllowNulls, prop.SQLType, _
                         prop.NETType, prop.MaxLength, prop.IsPrimaryKey, Nothing, Nothing, _
                         Nothing, prop.IsAutoIncrement, prop.IsComputed, prop.Precision, prop.Scale)
                Utility.CopyCustomAttributes(prop, buildColumn, recordset.BuildBuild)
                maintable.BuildBuildColumns.Add(buildColumn)
            End If
        Next
        Return tableOrdinal

    End Function

    Private Function AddJoinTables( _
             ByVal spSet As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectDef As MapObjectDef, _
             ByVal tableOrdinal As Int32, _
             ByVal joins As List(Of IMapJoin), _
             ByVal joinStyle As String, _
             ByVal recordset As BuildBuildRecordset) _
             As Int32
        Dim columnOrdinal As Int32 = 0
        ' Join base added above. 
        For Each join As IMapJoin In joins
            Dim wrapperBase As WrapperBase = TryCast(join, WrapperBase)
            Dim dbsTable As DbsTable = viewOrTable.DbsDataStructures.GetTable(join.TableName)
            Dim joinTable As New BuildBuildTable(spSet.BuildBuild, tableOrdinal, join.TableName, _
                     join.RightOriginalName, join.TableName, joinStyle, join.Alias, dbsTable.Schema)
            If dbsTable Is Nothing Then
                Utility.LogForUser("Can not find Join In Table", LogId.CanNotFindTable, objectDef, wrapperBase)
            Else
                tableOrdinal += 1
                recordset.BuildBuildTables.Add(joinTable)
                ' Backwards compatibility is the ordinal reset
                columnOrdinal = 0
                For Each column As MapJoinInColumn In join.MapJoinInColumns
                    Dim prop As MapProperty = objectDef.MapProperties.GetProperty(column.Name)
                    Dim dbsColumn As DbsTableColumn = dbsTable.DbsTableColumns(column.ColumnName)
                    columnOrdinal += 1
                    If prop Is Nothing Then
                        Utility.LogForUser("Can not find property", LogId.CanNotFindProperty, _
                                 objectDef, wrapperBase, column)
                    ElseIf dbsColumn Is Nothing Then
                        Utility.LogForUser("Can not find column", LogId.CanNotFindColumn, objectDef, _
                                 wrapperBase, column)
                    Else
                        Dim buildColumn As New BuildBuildColumn(spSet.BuildBuild, _
                                 column.Name, column.ColumnName, column.Alias, objectDef.IsUseForDesc(column.Name), _
                                 column.ColumnName, column.Caption, columnOrdinal, prop.AllowNulls, _
                                 prop.SQLType, prop.NETType, prop.MaxLength, dbsColumn.IsPrimaryKey, _
                                 String.Empty, String.Empty, String.Empty, prop.IsAutoIncrement, False, _
                                 prop.Precision, prop.Scale)
                        Utility.CopyCustomAttributes(column, buildColumn, recordset.BuildBuild)
                        joinTable.BuildBuildColumns.Add(buildColumn)

                    End If
                Next
            End If
        Next
    End Function

    Private Sub AddTableColumns( _
             ByVal spSet As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectDef As MapObjectDef, _
             ByVal leftAlias As String, _
             ByVal recordset As BuildBuildRecordset)

        Dim columnOrdinal As Int32 = 0
        For Each prop As MapProperty In objectDef.MapProperties
            If Not String.IsNullOrEmpty(prop.ColumnName) Or mDefaults.UseUnbackedInRetrieve Then
                Dim usefordesc As Boolean = (objectDef.MapUseForDescs(prop.Name) IsNot Nothing)
                columnOrdinal += 1
                Dim tableAlias As String = prop.TableAlias
                If String.IsNullOrEmpty(tableAlias) Then
                    tableAlias = prop.TableName
                End If
                ' 20070723 KAD This may have been put in place for a good reason, but it screws up 
                '              self joins, so I changed it 
                If String.Compare(prop.TableName, viewOrTable.Name, True) = 0 AndAlso String.Compare(prop.TableName, tableAlias) = 0 Then
                    tableAlias = leftAlias
                End If
                Dim dependentPropName As String = Nothing
                If Not String.IsNullOrEmpty(prop.DependentTableName) Then
                    Dim dependentProp As MapProperty = objectDef.GetPropertyByColumnName(prop.DependentColumnName)
                    If dependentProp IsNot Nothing Then
                        dependentPropName = dependentProp.Name
                    End If
                End If
                Dim buildColumn As New BuildTableColumn(spSet.BuildBuild, _
                         prop.Name, prop.TableName, prop.ColumnName, prop.ColumnAlias, _
                         usefordesc, prop.OriginalName, prop.Caption, columnOrdinal, prop.AllowNulls, _
                         prop.SQLType, prop.NETType, prop.MaxLength, prop.IsPrimaryKey, Nothing, Nothing, _
                         Nothing, prop.IsAutoIncrement, prop.IsComputed, prop.Precision, prop.Scale, _
                         viewOrTable.Schema, tableAlias, prop.DependentTableName, _
                         prop.DependentColumnName, dependentPropName, prop.JoinBaseRootKey, _
                         prop.JoinBaseLeafKey)
                Utility.CopyCustomAttributes(prop, buildColumn, recordset.BuildBuild)
                recordset.BuildTableColumns.Add(buildColumn)
            End If
        Next
    End Sub

    Protected Function AddBuildJoin( _
             ByVal spset As BuildSPSet, _
             ByVal join As IMapJoin, _
             ByVal viewOrTable As IViewOrTable) _
             As BuildJoin
        Dim buildJoin As BuildJoin
        With join
            Dim style As String
            If TypeOf join Is MapJoinBase Then
                style = "Base"
            Else
                style = "In"
            End If
            buildJoin = New BuildJoin(spset.BuildBuild, .JoinType, viewOrTable.Name, .TableName, .Alias, _
                     .LeftOriginalName, .LeftAlias, .RightOriginalName, style)
            Utility.CopyCustomAttributes(CType(join, WrapperBase), buildJoin, spset.BuildBuild)
            For Each joinKey As MapJoinKey In .MapJoinKeys
                Dim buildJoinKey As New BuildJoinKey(spset.BuildBuild, joinKey.LeftColumn, _
                         joinKey.RightColumn, joinKey.RightColumnKey, joinKey.SQLType, _
                         joinKey.IsAutoIncrement, joinKey.LeafKeyIsPrimary)
                Utility.CopyCustomAttributes(joinKey, buildJoinKey, spset.BuildBuild)
                buildJoin.BuildJoinKeys.Add(buildJoinKey)
            Next
        End With
        Return buildJoin
    End Function

    Protected Overridable Sub AddStoredProcs( _
             ByVal spset As BuildSPSet, _
             ByVal viewOrTable As IViewOrTable, _
             ByVal objectdef As MapObjectDef)
        ' TODO: Output all run sp's for stored procs to create
        '      Me.outputBuildSPSetXML(childNode)
        Dim name As String
        If Not objectdef.NoGenStoredProc.GetValueOrDefault And Not objectdef.NoGenAtAll.GetValueOrDefault Then
            ' TODO: Add additional retrieve and set select as requested in objectdef
            If objectdef.Generate.Contains("C") Then
                name = Utility.GetCreateSPName(Me.mDefaults, objectdef)
                Dim runSP As New BuildRunSP(spset.BuildBuild, name, "Create", False)
                AddColumnsAsParams(runSP, spset)
                spset.BuildRunSPs.Add(runSP)
            End If
            If objectdef.Generate.Contains("R") Then
                name = Utility.GetRetrieveSPName(Me.mDefaults, objectdef)
                Dim runSP As New BuildRunSP(spset.BuildBuild, name, "Retrieve", False)
                AddRetrieveParams(runSP, spset)
                spset.BuildRunSPs.Add(runSP)
                For Each criteria As MapSearchCriteria In objectdef.MapObjectSearchCriterias
                    If Not Utility.IsCriteriaPrimaryKey(criteria, objectdef) Then
                        name = Utility.GetRetrieveSPName(Me.mDefaults, objectdef, criteria)
                        runSP = New BuildRunSP(spset.BuildBuild, name, "Retrieve", True)
                        Utility.CopyCustomAttributes(criteria, runSP, spset.BuildBuild)
                        AddCriteriaParams(criteria, objectdef, runSP, spset)
                        spset.BuildRunSPs.Add(runSP)
                    End If
                Next
                For Each fKey As MapForeignKey In objectdef.MapForeignKeys
                    name = Utility.GetRetrieveChildSPName(Me.mDefaults, objectdef, fKey)
                    Dim tempParentTableName As String
                    Dim tempChildTableName As String
                    If fKey.MapChild Is Nothing Then
                        tempParentTableName = fKey.Name
                        tempChildTableName = objectdef.Name
                    Else
                        tempParentTableName = fKey.MapChild.TempParentTableName
                        tempChildTableName = fKey.MapChild.TempChildTableName
                    End If
                    runSP = New BuildRunSP(spset.BuildBuild, name, "RetrieveChild", False, _
                                                  tempParentTableName, tempChildTableName, _
                                                  fKey.MapParentObjectDef.Name, fKey.Name)
                    AddRetrieveChildrenParams(fKey, runSP, spset, objectdef)
                    AddRetrieveChildrenExtraColumns(fKey, runSP, spset, objectdef)
                    spset.BuildRunSPs.Add(runSP)
                Next
                If objectdef.HasSelfJoin.GetValueOrDefault() Then
                    name = Utility.GetRetrieveHierarchySPName(Me.mDefaults, objectdef)
                    runSP = New BuildRunSP(spset.BuildBuild, name, "RetrieveHierarchy", False)
                    AddRetrieveParams(runSP, spset)
                    spset.BuildRunSPs.Add(runSP)
                End If
            End If
            If objectdef.Generate.Contains("U") Then
                name = Utility.GetUpdateSPName(Me.mDefaults, objectdef)
                Dim runSP As New BuildRunSP(spset.BuildBuild, name, "Update", False)
                AddColumnsAsParams(runSP, spset)
                spset.BuildRunSPs.Add(runSP)
            End If
            If objectdef.Generate.Contains("D") Then
                name = Utility.GetDeleteSPName(Me.mDefaults, objectdef)
                Dim runSP As New BuildRunSP(spset.BuildBuild, name, "Delete", False)
                AddRetrieveParams(runSP, spset)
                AddTimeStampParam(runSP, spset)
                spset.BuildRunSPs.Add(runSP)
            End If
            If objectdef.Generate.Contains("S") Then
                name = Utility.GetSetSelectSPName(Me.mDefaults, objectdef)
                Dim runSP As New BuildRunSP(spset.BuildBuild, name, "SetSelect", False)
                spset.BuildRunSPs.Add(runSP)
                For Each criteria As MapSearchCriteria In objectdef.MapSetSearchCriterias
                    name = Utility.GetSetSelectSPName(Me.mDefaults, objectdef, criteria)
                    runSP = New BuildRunSP(spset.BuildBuild, name, "SetSelect", True)
                    Utility.CopyCustomAttributes(criteria, runSP, spset.BuildBuild)
                    AddCriteriaParams(criteria, objectdef, runSP, spset)
                    spset.BuildRunSPs.Add(runSP)
                Next
            End If
        End If
    End Sub

    Private Sub AddColumnsAsParams(ByVal runSP As BuildRunSP, ByVal spset As BuildSPSet)
        ' Only save data for the first recordset
        Dim recordset As BuildBuildRecordset = spset.BuildBuildRecordsets(0)
        Dim i As Int32 = 0
        Dim tableNames As New Dictionary(Of String, String)
        For Each table As BuildBuildTable In recordset.BuildBuildTables
            i += 1
            If i = 1 OrElse table.JoinStyle.ToLower() = "base" Then
                tableNames.Add(table.OriginalName, table.Name)
                'For Each column As BuildBuildColumn In table.BuildBuildColumns
                '   Dim param As New BuildRunSPParam(spset.BuildBuild, _
                '         column.Name, column.Column, table.Name, table.OriginalName, _
                '         column.SQLType, column.NETType, column.MaxLength, column.Precision, _
                '         column.Scale, column.IsPrimaryKey, column.IsAutoIncrement, _
                '         column.AllowNulls)
                '   Utility.CopyCustomAttributes(column, param, runSP.BuildBuild)
                '   runSP.BuildRunSPParams.Add(param)
                'Next
            End If
        Next
        For Each column As BuildTableColumn In recordset.BuildTableColumns
            If tableNames.ContainsKey(column.TableName) Then
                Dim param As New BuildRunSPParam(spset.BuildBuild, _
                            column.Name, column.Column, tableNames(column.TableName), column.TableName, _
                            column.SQLType, column.NETType, column.MaxLength, column.Precision, _
                            column.Scale, column.IsPrimaryKey, column.IsAutoIncrement, _
                            column.AllowNulls, column.DependentTableName, column.DependentColumnName, _
                            column.JoinBaseRootKey, column.JoinBaseLeafKey)
                runSP.BuildRunSPParams.Add(param)
            End If
        Next
    End Sub

    Private Sub AddRetrieveParams( _
             ByVal runSP As BuildRunSP, _
             ByVal spset As BuildSPSet)
        For Each param As BuildRetrieveParameter In spset.BuildRetrieveParameters
            'Dim buildColumn As BuildBuildColumn = spset.GetColumn(param.Name)
            Dim tableColumn As BuildTableColumn = spset.GetTableColumn(spset.OriginalName, param.Column)
            Dim buildTable As BuildBuildTable = spset.BuildBuildRecordsets(0).BuildBuildTables.GetBuildTableForColumn(param.Name)
            'Dim buildParam As New BuildRunSPParam(spset.BuildBuild, param.Name, _
            '            param.Column, buildTable.Name, buildTable.OriginalName, param.SQLType, param.NETType, _
            '            param.MaxLength, buildColumn.Precision, buildColumn.Scale, param.IsPrimaryKey, _
            '            param.IsAutoIncrement, buildColumn.AllowNulls)
            Dim buildParam As New BuildRunSPParam(spset.BuildBuild, param.Name, _
                        param.Column, buildTable.Name, buildTable.OriginalName, param.SQLType, param.NETType, _
                        param.MaxLength, tableColumn.Precision, tableColumn.Scale, param.IsPrimaryKey, _
                        param.IsAutoIncrement, tableColumn.AllowNulls, tableColumn.DependentTableName, _
                        tableColumn.DependentColumnName, tableColumn.JoinBaseRootKey, tableColumn.JoinBaseLeafKey)
            Utility.CopyCustomAttributes(param, buildParam, runSP.BuildBuild)
            runSP.BuildRunSPParams.Add(buildParam)
        Next
    End Sub

    Private Sub AddNewRetrieveParameter( _
             ByVal propertyName As String, _
             ByVal wrapperBase As WrapperBase, _
             ByVal objectDef As MapObjectDef, _
             ByVal runSP As BuildRunSP, _
             ByVal spset As BuildSPSet)
        Dim prop As MapProperty = objectDef.MapProperties.GetProperty(propertyName)
        If prop Is Nothing Then
            Utility.LogForUser("Criterion not found", LogId.CanNotFindCriteria, objectDef, wrapperBase)
        Else
            Dim param As New BuildRunSPParam(spset.BuildBuild, _
                              prop.Name, prop.ColumnName, prop.TableName, _
                              prop.TableName, prop.SQLType, _
                              prop.NETType, prop.MaxLength, prop.Precision, _
                              prop.Scale, prop.IsPrimaryKey, prop.IsAutoIncrement, _
                              prop.AllowNulls, prop.DependentTableName, prop.DependentColumnName, _
                              prop.JoinBaseRootKey, prop.JoinBaseLeafKey)
            Utility.CopyCustomAttributes(wrapperBase, param, runSP.BuildBuild)
            runSP.BuildRunSPParams.Add(param)
        End If
    End Sub

    Private Sub AddRetrieveChildrenParams( _
             ByVal fKey As MapForeignKey, _
             ByVal runSP As BuildRunSP, _
             ByVal spset As BuildSPSet, _
             ByVal objectDef As MapObjectDef)
        For Each key As MapRelationKey In fKey.MapRelationKeys
            Dim propName As String = key.ChildProperty
            If String.IsNullOrEmpty(propName) Then
                propName = key.ChildColumn
            End If
            AddNewRetrieveParameter(propName, key, objectDef, runSP, spset)
        Next
    End Sub

    Private Sub AddRetrieveChildrenExtraColumns( _
             ByVal fKey As MapForeignKey, _
             ByVal runSP As BuildRunSP, _
             ByVal spset As BuildSPSet, _
             ByVal objectDef As MapObjectDef)
        For Each key As MapRelationKey In fKey.MapRelationKeys
            Dim propName As String = key.ChildProperty
            Dim prop As MapProperty = objectDef.MapProperties.GetProperty(propName)
            If prop Is Nothing Then
                ' Assume this is an extra column
                Dim parentObjectDef As MapObjectDef = objectDef.MapMapping.GetObjectDef(key.ParentObject)
                Dim parentProp As MapProperty = parentObjectDef.MapProperties.GetProperty(key.ParentProperty)
                If parentProp Is Nothing Then
                    parentObjectDef = objectDef.MapMapping.GetObjectDef(key.ParentAlias)
                    parentProp = parentObjectDef.MapProperties.GetProperty(key.ParentProperty)
                End If
                If parentProp Is Nothing Then
                    Utility.LogForUser("ParentProp not found", LogId.CanNotFindProperty, objectDef, fKey)
                    Continue For
                End If
                ' KAD 20071125: Changed from parent to child and changed to use new variable below
                Dim childAlias As String = key.ChildAlias
                If String.IsNullOrEmpty(childAlias) Then
                    childAlias = key.ChildTable
                End If
                'Dim parentAlias As String = parentProp.TableAlias
                'If String.IsNullOrEmpty(parentAlias) Then
                '    parentAlias = parentProp.TableName
                'End If
                Dim extraColumn As New BuildTableColumn(spset.BuildBuild, _
                       key.ChildProperty, key.ChildTable, key.ChildColumn, key.ChildProperty, _
                       False, key.ChildColumn, parentProp.Caption, -1, False, parentProp.SQLType, _
                       parentProp.NETType, parentProp.MaxLength, False, Nothing, Nothing, Nothing, _
                       False, False, parentProp.Precision, parentProp.Scale, parentProp.Schema, _
                       childAlias, Nothing, Nothing, Nothing, False, False)
                runSP.BuildExtraColumns.Add(extraColumn)
            End If
        Next
    End Sub

    Private Sub AddCriteriaParams( _
             ByVal criteria As MapSearchCriteria, _
             ByVal objectDef As MapObjectDef, _
             ByVal runSP As BuildRunSP, _
             ByVal spset As BuildSPSet)
        For Each criterion As MapSearchCriterion In criteria.MapSearchCriterions
            ' 20070816 Changed from criteria.name to criterion.name 
            AddNewRetrieveParameter(criterion.Name, criteria, objectDef, runSP, spset)
        Next
    End Sub

    Private Sub AddTimeStampParam(ByVal runSP As BuildRunSP, ByVal spset As BuildSPSet)
        Dim table As BuildBuildTable = spset.BuildBuildRecordsets(0).BuildBuildTables(0)
        For Each column As BuildBuildColumn In table.BuildBuildColumns
            If column.SQLType.ToLower() = "timestamp" Then
                ' I'm assuming a timestamp parameter is never dependent
                Dim param As New BuildRunSPParam(spset.BuildBuild, _
                               column.Name, column.Column, table.Name, table.OriginalName, _
                               column.SQLType, column.NETType, column.MaxLength, column.Precision, _
                               column.Scale, column.IsPrimaryKey, column.IsAutoIncrement, _
                               column.AllowNulls, Nothing, Nothing, Nothing, Nothing)
                Utility.CopyCustomAttributes(column, param, runSP.BuildBuild)
                runSP.BuildRunSPParams.Add(param)
            End If
        Next
    End Sub

End Class
